import os
import re
import shutil
import pandas as pd
from tkinter import Tk, filedialog, messagebox, simpledialog, Listbox, MULTIPLE, END, Button, Label, Frame, Text, \
    Scrollbar
from fuzzywuzzy import fuzz  # 需要安装：pip install fuzzywuzzy python-Levenshtein

# 支持的扩展名列表
VALID_EXTENSIONS = ['.xls', '.xlsx', '.doc', '.docx', '.pdf', '.csv']


def select_excel_file():
    """选择Excel文件并返回路径"""
    root = Tk()
    root.withdraw()
    file_path = filedialog.askopenfilename(
        title="选择Excel文件",
        filetypes=[("Excel文件", "*.xls *.xlsx")]
    )
    root.destroy()
    return file_path


def get_sheets(excel_path):
    """获取Excel中的所有sheet名称"""
    return pd.ExcelFile(excel_path).sheet_names


def select_sheets(sheets, exclude=None):
    """让用户选择要处理的sheet，排除指定的sheet"""
    if exclude is None:
        exclude = []

    root = Tk()
    root.title("选择Sheet")

    Label(root, text="请选择要处理的sheet（可多选）:").pack(padx=10, pady=5)
    listbox = Listbox(root, selectmode=MULTIPLE, width=50, height=15)
    for sheet in sheets:
        if sheet not in exclude:
            listbox.insert(END, sheet)
    listbox.pack(padx=10, pady=5)

    selected_sheets = []

    def on_confirm():
        nonlocal selected_sheets
        selected_sheets = [listbox.get(i) for i in listbox.curselection()]
        root.destroy()

    Button(root, text="确认", command=on_confirm).pack(pady=10)
    root.mainloop()
    return selected_sheets


def select_target_folder():
    """选择目标文件夹"""
    root = Tk()
    root.withdraw()
    folder = filedialog.askdirectory(title="选择保存文件的目标文件夹")
    root.destroy()
    return folder


def remove_digits_and_punctuation(text):
    """去除文本中的数字和标点符号，只保留汉字"""
    # 移除非汉字字符（保留汉字、字母）
    cleaned = re.sub(r'[^\w\u4e00-\u9fff]', '', text)
    # 去除数字
    cleaned = re.sub(r'\d+', '', cleaned)
    return cleaned


def find_first_file(source_folder, report_name):
    """在文件夹树中递归查找第一个匹配的文件（只匹配汉字部分）"""
    # 清理报表名称：去除数字和标点，只保留汉字
    cleaned_report = remove_digits_and_punctuation(report_name)
    if not cleaned_report:
        cleaned_report = report_name

    # 核心匹配逻辑
    for root_dir, _, files in os.walk(source_folder):
        for file in files:
            file_name, ext = os.path.splitext(file)
            if ext.lower() not in VALID_EXTENSIONS:
                continue

            # 清理文件名：去除数字和标点，只保留汉字
            cleaned_filename = remove_digits_and_punctuation(file_name)

            # 方法1：使用模糊匹配分数（部分匹配）
            similarity = fuzz.partial_ratio(cleaned_report.lower(), cleaned_filename.lower())

            # 方法2：检查是否包含所有关键词（顺序无关）
            contains_all_keywords = True
            report_words = cleaned_report.split()
            if not report_words:  # 如果拆分后为空，使用整个字符串
                report_words = [cleaned_report]

            for word in report_words:
                if word.lower() not in cleaned_filename.lower():
                    contains_all_keywords = False
                    break

            # 方法3：检查核心词序列是否按顺序出现
            sequential_match = True
            last_index = -1
            for char in cleaned_report:
                current_index = cleaned_filename.lower().find(char.lower(), last_index + 1)
                if current_index == -1:
                    sequential_match = False
                    break
                last_index = current_index

            # 如果满足任一匹配条件，则返回第一个匹配文件
            if similarity >= 80 or contains_all_keywords or sequential_match:
                print(f"匹配成功: '{report_name}' -> 文件名: '{file_name}' (清理后: '{cleaned_filename}')")
                return os.path.join(root_dir, file)

    # 没有找到匹配文件
    print(f"未匹配: '{report_name}' (清理后: '{cleaned_report}')")
    return None


def load_mapping(excel_path, mapping_sheet="Sheet2"):
    """加载映射表，返回映射字典"""
    try:
        # 尝试读取映射表
        mapping_df = pd.read_excel(excel_path, sheet_name=mapping_sheet)

        # 检查必要的列
        if "原始区划" not in mapping_df.columns or "映射路径" not in mapping_df.columns:
            messagebox.showwarning("映射表格式错误",
                                   f"映射表 '{mapping_sheet}' 缺少必要的列('原始区划'或'映射路径')")
            return {}

        # 创建映射字典
        mapping_dict = {}
        for _, row in mapping_df.iterrows():
            original = str(row["原始区划"]).strip()
            mapped = str(row["映射路径"]).strip()
            if original and mapped:
                mapping_dict[original] = mapped
                print(f"映射: '{original}' -> '{mapped}'")

        return mapping_dict

    except Exception as e:
        messagebox.showinfo("映射表加载", f"未找到或无法加载映射表 '{mapping_sheet}': {str(e)}")
        return {}


def apply_mapping(source_path, mapping_dict):
    """应用映射到源路径"""
    # 尝试精确匹配
    if source_path in mapping_dict:
        return mapping_dict[source_path]

    # 尝试部分匹配（处理路径前缀）
    for original, mapped in mapping_dict.items():
        if source_path.startswith(original):
            # 替换路径前缀
            return source_path.replace(original, mapped, 1)

    # 没有匹配的映射，返回原路径
    return source_path


def show_unmatched_report(unmatched_reports):
    """显示未匹配到的报表名称"""
    if not unmatched_reports:
        return

    root = Tk()
    root.title("未匹配报表列表")
    root.geometry("600x400")

    Label(root, text="以下报表未找到匹配文件:", font=("Arial", 12, "bold")).pack(pady=10)

    # 创建文本框和滚动条
    text_frame = Frame(root)
    text_frame.pack(fill="both", expand=True, padx=10, pady=5)

    scrollbar = Scrollbar(text_frame)
    scrollbar.pack(side="right", fill="y")

    text_area = Text(text_frame, wrap="word", yscrollcommand=scrollbar.set)
    text_area.pack(fill="both", expand=True)
    scrollbar.config(command=text_area.yview)

    # 添加未匹配报表名称
    for report in unmatched_reports:
        text_area.insert("end", f"- {report}\n")

    text_area.config(state="disabled")

    Button(root, text="关闭", command=root.destroy).pack(pady=10)
    root.mainloop()


def main():
    # 1. 选择Excel文件
    excel_path = select_excel_file()
    if not excel_path:
        return

    # 2. 加载映射表（从Sheet2）
    mapping_dict = load_mapping(excel_path, mapping_sheet="Sheet2")

    # 3. 获取并选择sheet（排除映射表）
    all_sheets = get_sheets(excel_path)
    if not all_sheets:
        messagebox.showerror("错误", "Excel文件中没有找到任何sheet")
        return

    # 排除映射表sheet
    exclude_sheets = ["Sheet2"]  # 可以根据需要添加更多要排除的sheet
    selected_sheets = select_sheets(all_sheets, exclude=exclude_sheets)
    if not selected_sheets:
        return

    # 4. 选择目标文件夹
    target_folder = select_target_folder()
    if not target_folder:
        return

    # 5. 创建目标文件夹（如果不存在）
    os.makedirs(target_folder, exist_ok=True)

    # 6. 处理每个选中的sheet
    all_matched_files = []
    unmatched_reports = []  # 存储未匹配的报表名称

    for sheet_name in selected_sheets:
        try:
            df = pd.read_excel(excel_path, sheet_name=sheet_name)

            # 检查必要的列是否存在
            if "上报区划" not in df.columns or "报表名称" not in df.columns:
                messagebox.showwarning("警告", f"Sheet '{sheet_name}' 缺少必要的列('上报区划'或'报表名称')")
                continue

            # 处理每一行数据
            for index, row in df.iterrows():
                original_path = str(row["上报区划"]).strip()
                report_name = str(row["报表名称"]).strip()

                if not original_path or not report_name:
                    continue

                # 应用映射
                source_path = apply_mapping(original_path, mapping_dict)
                print(f"原始路径: {original_path} -> 映射后路径: {source_path}")

                # 检查源路径是否存在
                if not os.path.exists(source_path):
                    print(f"路径不存在: {source_path} (原始路径: {original_path})")
                    unmatched_reports.append(f"{report_name} (路径不存在: {source_path})")
                    continue

                # 查找匹配的文件（只找第一个匹配项）
                matched_file = find_first_file(source_path, report_name)

                if matched_file:
                    all_matched_files.append(matched_file)

                    # 复制文件到目标文件夹
                    try:
                        base_name = os.path.basename(matched_file)
                        name, ext = os.path.splitext(base_name)
                        target_path = os.path.join(target_folder, base_name)

                        # 如果目标文件已存在，添加计数器
                        counter = 1
                        while os.path.exists(target_path):
                            target_name = f"{name}_{counter}{ext}"
                            target_path = os.path.join(target_folder, target_name)
                            counter += 1

                        shutil.copy2(matched_file, target_path)
                        print(f"已复制: {matched_file} -> {target_path}")

                    except Exception as e:
                        print(f"复制文件失败 {matched_file}: {str(e)}")
                else:
                    # 记录未匹配的报表名称
                    unmatched_reports.append(report_name)

        except Exception as e:
            messagebox.showerror("处理错误", f"处理sheet '{sheet_name}'时出错: {str(e)}")

    # 显示完成信息
    message = f"处理完成!\n\n共找到并复制了 {len(all_matched_files)} 个文件\n到目录: {target_folder}"

    if unmatched_reports:
        message += f"\n\n有 {len(unmatched_reports)} 个报表未找到匹配文件"
        # 显示未匹配报表窗口
        show_unmatched_report(unmatched_reports)

    messagebox.showinfo("完成", message)


if __name__ == "__main__":
    main()